package com.internetCafes.spms.web.certificate.controller;

import com.internetCafes.spms.common.AIRecognize.recognizer.impl.QRCodeRecognizer;
import com.internetCafes.spms.common.CommonConstants;
import com.internetCafes.spms.common.module.excel.ExcelUtils;
import com.internetCafes.spms.common.module.excel.po.*;
import com.internetCafes.spms.common.utils.DateUtils;
import com.internetCafes.spms.common.utils.ExcelImportUtil;
import com.internetCafes.spms.common.utils.ShiroUtils;
import com.internetCafes.spms.common.utils.entity.RR;
import com.internetCafes.spms.core.annotation.SysLog;
import com.internetCafes.spms.core.exception.RRException;
import com.internetCafes.spms.core.exception.RRExceptionCodeEnum;
import com.internetCafes.spms.core.model.pageModel.DataGrid;
import com.internetCafes.spms.core.utils.FileUtils;
import com.internetCafes.spms.core.utils.IdWorker;
import com.internetCafes.spms.core.utils.Query;
import com.internetCafes.spms.core.utils.R;
import com.internetCafes.spms.web.certificate.bizservice.CertTransactionBizService;
import com.internetCafes.spms.web.certificate.dao.CertificateInfoMapper;
import com.internetCafes.spms.web.certificate.model.certificate.req.CertTransactionInfoReq;
import com.internetCafes.spms.web.certificate.model.dto.transaction.BorrowOutReturnBatchDTO;
import com.internetCafes.spms.web.certificate.model.vo.airecognize.CertificateSubmitAIRecognizeBatchVO;
import com.internetCafes.spms.web.certificate.model.vo.airecognize.RecognizeBatchSaveVO;
import com.internetCafes.spms.web.certificate.entity.CertiTransactionInfo;
import com.internetCafes.spms.web.certificate.entity.CertificateInfo;
import com.internetCafes.spms.web.certificate.entity.CertificateRenewalInfo;
import com.internetCafes.spms.web.certificate.entity.EmployeeInfo;
import com.internetCafes.spms.web.certificate.service.*;
import com.internetCafes.spms.web.certificate.util.filter.CertificateFilter;
import com.internetCafes.spms.web.certificate.vo.*;
import com.internetCafes.spms.web.customer.common.cont.CommonConst;
import com.internetCafes.spms.web.customer.service.user.IEntUserInfoService;
import com.internetCafes.spms.web.sys.model.ConfInfo;
import com.internetCafes.spms.web.sys.model.ResourceInfo;
import com.internetCafes.spms.web.sys.model.UserInfo;
import com.internetCafes.spms.web.sys.service.ConfInfoService;
import com.internetCafes.spms.web.sys.service.ResourceInfoService;
import com.sms.common.util.hutool.core.collection.CollectionUtil;
import com.sms.common.util.hutool.core.util.ObjectUtil;
import com.sms.common.util.hutool.core.util.StrUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.jeecgframework.poi.excel.entity.ImportSheetParams;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.stream.Collectors;


/**
 * 证书信息表
 *
 * @author Sunny
 */
@Api(tags = {"证书信息列表接口"})
@RestController
@RequestMapping("/certificate/certificateInfo")
public class CertificateInfoController {

    @Autowired
    private CertificateInfoService certificateInfoService;

    @Autowired
    private ConfInfoService confInfoService;

    @Autowired
    private ResourceInfoService resourceInfoService;

    @Autowired
    private CertiTransactionInfoService certiTransactionInfoService;

    @Autowired
    private CertificateRenewalInfoService certificateRenewalInfoService;

    @Autowired
    private QRCodeRecognizer qrCodeRecognizer;

    @Autowired
    private EmployeeInfoService employeeInfoService;

    @Autowired
    private CertificateInfoMapper certificateInfoMapper;

    @Autowired
    private CertificateSubmitAIRecognizeService certificateSubmitAIRecognizeService;

    @Autowired
    private IEntUserInfoService userInfoService;

    @Value("${system.upload.pic}")
    private String picPath;

    @Value("${system.remote.url}")
    private String remoteUrl;

    @Value("${system.upload.others}")
    private String othersPath;

    @Autowired
    private CertTransactionBizService transactionBizService;

    private Logger logger = LoggerFactory.getLogger(getClass());


    /**
     * 批量还回证书
     *
     * @author Zj
     */
    @SysLog("批量还回证书")
    @ApiOperation(value = "批量还回证书")
    @PostMapping("/borrowOut/return/batch")
    @RequiresPermissions("certificate:certificateInfo:update")
    public RR<Void> borrowOutReturnBatch(@RequestBody BorrowOutReturnBatchDTO dto) {
        // 获取证书
        if (org.apache.commons.collections.CollectionUtils.isEmpty(dto.getIds())) {
            throw new RRException("请选择证书");
        }
        Map<String, Object> params = new HashMap<>();
        params.put("ids", dto.getIds());
        List<CertificateInfo> certPoList = certificateInfoService.query(params);
        CertTransactionInfoReq req = new CertTransactionInfoReq();
        req.setCertIdList(dto.getIds());
        req.setCertInfoPoList(certPoList);
        req.setRemark(dto.getRemark());
        transactionBizService.borrowOutReturnBatchByCertIdList(req);
        // certificateInfoService.borrowOutReturnBatch(dto);
        return RR.success("还回成功");
    }

    /**
     * 批量新增识别后的证书
     *
     * @author Zj
     */
    @SysLog("批量新增识别后的证书")
    @PostMapping("/save/batch")
    @RequiresPermissions("certificate:certificateInfo:save")
    public R saveBatch(@RequestBody List<CertificateInfoVo> certificateInfos) {
        RecognizeBatchSaveVO result = certificateSubmitAIRecognizeService.saveBatch(certificateInfos);
        return R.ok().put("data", result);
    }

    /**
     * 批量智能识别人员证书
     *
     * @author Zj
     */
    @SysLog("批量智能识别人员证书")
    @ApiOperation(value = "批量智能识别人员证书")
    @PostMapping("/AIRecognize/batch")
    @RequiresPermissions("certificate:certificateInfo:save")
    public R AIRecognizeBatch(@RequestParam("files") MultipartFile[] files) {
        CertificateSubmitAIRecognizeBatchVO result = certificateSubmitAIRecognizeService.CertificateSubmitAIRecognizeBatch(files);
        return R.ok().put("data", result);
    }

    /**
     * 列表
     */
    @RequestMapping("/list")
    @RequiresPermissions("certificate:certificateInfo:list")
    public R list(@RequestParam Map<String, Object> params, HttpServletRequest request) throws UnsupportedEncodingException {

        Query query = new Query(params);


        String type = request.getParameter("type");
        String tenantId = request.getParameter("tenantId");
        String status = request.getParameter("status");
        String certificateType = request.getParameter("certificateType");
        String registerType = request.getParameter("registerType");
        String keepDeptId = request.getParameter("keepDeptId");
        String keepUserId = request.getParameter("keepUserId");
        String name = request.getParameter("name");
        String idType = request.getParameter("idType");
        String pIdType = request.getParameter("pIdType");
        String idCard = request.getParameter("idCard");
        String certificateNo = params.get("certificateNo") == null ? "" : params.get("certificateNo").toString();
        String mobile = request.getParameter("mobile");
        String profession = request.getParameter("profession");
        String academic = request.getParameter("academic");
        String releaseTime = request.getParameter("releaseTime");
        String validityEndTime = request.getParameter("validityEndTime");
        String validityType = request.getParameter("validityType");
        String months = request.getParameter("months");
        String days = request.getParameter("days");
        String orderBy = request.getParameter("orderBy");
        String inOrder = request.getParameter("inOrder");
        String friendshipEmployeeId = request.getParameter("friendshipEmployeeId");
        String empOrFriId = request.getParameter("empOrFriId");
        String wechatParam = request.getParameter("wechatParam");
        String projectName = request.getParameter("projectName");
        String warrantFlag = request.getParameter("warrantFlag");
        String borrowOutFlag = request.getParameter("borrowOutFlag");
        params = new HashMap<>();

        if (StringUtils.isNotBlank(borrowOutFlag)) {
            params.put("borrowOutFlag", borrowOutFlag);
        }

        if (StringUtils.isNotBlank(warrantFlag)) {
            params.put("warrantFlag", warrantFlag);
        }

        if (StringUtils.isNotBlank(projectName)) {
            params.put("projectName", projectName);
        }

        if (StringUtils.isNotBlank(wechatParam)) {
            params.put("wechatParam", wechatParam);
        }

        if (StringUtils.isNotBlank(certificateType)) {
            params.put("certificateType", Integer.parseInt(certificateType));
        }

        if (StringUtils.isNotBlank(registerType)) {
            params.put("registerType", Integer.parseInt(registerType));
        }

        if (StringUtils.isNotBlank(empOrFriId)) {
            params.put("empOrFriId", empOrFriId);
        }

        if (StringUtils.isNotBlank(friendshipEmployeeId)) {
            params.put("friendshipEmployeeId", Long.parseLong(friendshipEmployeeId));
        }

        if (StringUtils.isNotBlank(keepDeptId)) {
            params.put("keepDeptId", Long.parseLong(keepDeptId));
        }

        if (StringUtils.isNotBlank(keepUserId)) {
            params.put("keepUserId", Long.parseLong(keepUserId));
        }

        if (StringUtils.isNotBlank(name)) {
            params.put("name", name);
        }

        if (StringUtils.isNotBlank(idType)) {
            params.put("idType", idType);
        }

        if (StringUtils.isNotBlank(pIdType)) {
            params.put("pIdType", pIdType);
        }

        if (StringUtils.isNotBlank(idCard)) {
            params.put("idCard", idCard);
        }

        if (StringUtils.isNotBlank(certificateNo)) {
            params.put("certificateNo", certificateNo);
        }

        if (StringUtils.isNotBlank(mobile)) {
            params.put("mobile", mobile);
        }

        if (StringUtils.isNotBlank(profession)) {
            params.put("profession", profession);
        }

        if (StringUtils.isNotBlank(academic)) {
            params.put("academic", academic);
        }

        if (StringUtils.isNotBlank(releaseTime)) {
            params.put("releaseTime", DateUtils.strToDate(releaseTime));
        }

        if (StringUtils.isNotBlank(validityEndTime)) {
            params.put("validityEndTime", DateUtils.strToDate(validityEndTime));
        }

        if (StringUtils.isNotBlank(type)) {
            params.put("confType", type);
        }

        if (StringUtils.isNotBlank(tenantId)) {
            params.put("tenantId", tenantId);
        } else {
            UserInfo userInfo = ShiroUtils.getUserEntity();
            if (ObjectUtil.isNotNull(userInfo.getTenantId()) && !userInfo.getTenantId().equals(0)) {
                params.put("tenantId", userInfo.getTenantId());
            }
        }

        if (StringUtils.isNotBlank(orderBy)) {
            if ("warningCount".equals(orderBy)) {
                params.put("orderBy", "validity_end_time");
            } else {
                params.put("orderBy", com.internetCafes.spms.common.utils.StringUtils.HumpToUnderline(orderBy));
            }
        }

        if (StringUtils.isNotBlank(inOrder)) {
            params.put("inOrder", inOrder);
        }

        if (StringUtils.isNotBlank(validityType)) {
            switch (validityType) {
                case "0":
                    params.put("beIvalidity", com.internetCafes.spms.core.utils.DateUtils.getBeAfMonth(Integer.parseInt(months)));
                    break;
                case "-1":
                    params.put("ivalidity", "ivalidity");
                    break;
                case "2":
                    if (StringUtils.isNotBlank(months)) {
                        params.put("warn", com.internetCafes.spms.core.utils.DateUtils.getBeAfMonth(Integer.parseInt(months)));
                    }
                    if (StringUtils.isNotBlank(days)) {
                        params.put("warn", com.internetCafes.spms.core.utils.DateUtils.getBeAfDay(Integer.parseInt(days)));
                    }
                    break;
                default:
                    params.put("validity", com.internetCafes.spms.core.utils.DateUtils.getBeAfMonth(Integer.parseInt(months)));
                    break;
            }
        }

        if (StringUtils.isNotBlank(status)) {
            if ("crash".equals(status)) {
                params.put("status", CommonConstants.CertificateStatus.DELETE);
            } else {
                params.put("status", Integer.parseInt(status));
            }
        } else {
            params.put("statuses", CommonConstants.CertificateStatus.EXCEPT_DELETE);
        }

        DataGrid dataGrid = certificateInfoService.dataGrid(params, query.getPage(), query.getLimit());

        return R.ok().put("page", dataGrid);
    }


    @RequestMapping("/getJobs")
    @RequiresPermissions("certificate:certificateInfo:list")
    public List<ConfInfo> getJobs() {
        Map<String, Object> params = new HashMap<>();
        params.put("type", CommonConstants.CONF_Certificate_Type);
        params.put("status", CommonConstants.CommonStatus.ENABLE);
        List<ConfInfo> query = confInfoService.query(params);
        ConfInfo confInfo = new ConfInfo();
        confInfo.setName("全部");
        query.add(0, confInfo);
        return query;
    }


    @RequestMapping("/getRingCollect")
    @RequiresPermissions("certificate:certificateInfo:list")
    public List<CollectInfo> getRingCollect() {
        Map<String, Object> params = new HashMap<>();
        return certificateInfoService.collectAllByJobParams(params);
    }


    @RequestMapping("/getCertDetailCollect")
    @RequiresPermissions("certificate:certificateInfo:list")
    public List<CollectInfo> getCertDetailCollect() {

        Map<String, Object> params = new HashMap<>();
        List<CollectInfo> collectInfoList = new ArrayList<>();
        List<CollectInfo> partJobCertList = certificateInfoService.partJobCertByJobParams(params);

        List<CollectInfo> passedCertList = certificateInfoService.passCertByJobParams(params);

        List<CollectInfo> willPassCertList = certificateInfoService.willPassCertByJobParams(params);
        if (CollectionUtils.isNotEmpty(willPassCertList)) {
            collectInfoList.addAll(willPassCertList);
        }

        if (CollectionUtils.isNotEmpty(passedCertList)) {
            collectInfoList.addAll(passedCertList);
        }

        if (CollectionUtils.isNotEmpty(partJobCertList)) {
            collectInfoList.addAll(partJobCertList);
        }

        return collectInfoList;
    }


    @RequestMapping("/bbCertByJobCollect")
    @RequiresPermissions("certificate:certificateInfo:list")
    public List<CollectInfo> bbCertByJobCollect() {

        Map<String, Object> params = new HashMap<>();
        List<CollectInfo> collectInfoList = new ArrayList<>();
        List<CollectInfo> bringCertList = certificateInfoService.bringCertByJobParams(params);

        List<CollectInfo> borrowCertList = certificateInfoService.borrowCertByJobParams(params);

        List<String> dateList = new ArrayList<>();
        if (CollectionUtils.isEmpty(bringCertList) && CollectionUtils.isEmpty(borrowCertList)) {
            return collectInfoList;
        }

        bringCertList.forEach((b) -> {
            if (b.getD() != null) {
                dateList.add(b.getD());
            }
        });

        borrowCertList.forEach((b) -> {
            if (!dateList.contains(b.getD()) && b.getD() != null) {
                dateList.add(b.getD());
            }
        });

        dateList.forEach((d) -> {
            CollectInfo co = new CollectInfo();
            co.setD(d);
            int num = 0;
            for (CollectInfo c : bringCertList) {
                if (c.getD() != null && c.getD().equals(d)) {
                    num = c.getNum();
                }
            }

            co.setBringNum(num);
            num = 0;
            for (CollectInfo c : borrowCertList) {
                if (c.getD() != null && c.getD().equals(d)) {
                    num = c.getNum();
                }
            }
            co.setBorrowNum(num);
            collectInfoList.add(co);
        });

        return collectInfoList;
    }


    @RequestMapping("/getJobsConf")
    @RequiresPermissions("certificate:certificateInfo:list")
    public List<ConfInfo> getJobsConf(String type) {
        List<ConfInfo> confInfoList = null;
        if (StringUtils.isNotBlank(type)) {
            Map<String, Object> params = new HashMap<>();
            params.put("type", type);
            params.put("status", CommonConstants.CommonStatus.ENABLE);
            confInfoList = confInfoService.query(params);
        }
        return confInfoList;
    }


    @RequestMapping("/getJobsById")
    @RequiresPermissions("certificate:certificateInfo:list")
    public List<CertificateInfoVo> getJobsById(Long id, String status) {
        CertificateInfo c = certificateInfoService.findById(id);
        if (c == null) {
            return null;
        }
        Map<String, Object> params = new HashMap<>();
        params.put("employeeId", c.getEmployeeId());
        if (StringUtils.isNotBlank(status) && "crash".equals(status)) {
            params.put("status", CommonConstants.CertificateStatus.DELETE);
        } else {
            params.put("statuses", CommonConstants.CertificateStatus.EXCEPT_DELETE);
        }

        List<CertificateInfoVo> certificateInfoVoList = certificateInfoService.selectByJobParams(params);
        Map<String, Object> _params = new HashMap<>();
        certificateInfoVoList.forEach((vo) -> {
            _params.put("moduleId", vo.getId());
            _params.put("moduleCode", CommonConstants.ResourceInfo_PIC_Certi);
            List<ResourceInfo> resourceInfoList = resourceInfoService.query(_params);
            if (CollectionUtils.isNotEmpty(resourceInfoList)) {
                vo.setCertifInfoList(resourceInfoList);
            }

            _params.put("moduleCode", CommonConstants.ResourceInfo_PIC_Constrat);
            resourceInfoList = resourceInfoService.query(_params);
            if (CollectionUtils.isNotEmpty(resourceInfoList)) {
                vo.setContractInfoList(resourceInfoList);
            }

            _params.put("moduleCode", CommonConstants.ResourceInfo_PIC_Pay);
            resourceInfoList = resourceInfoService.query(_params);
            if (CollectionUtils.isNotEmpty(resourceInfoList)) {
                vo.setPayInfoList(resourceInfoList);
            }

            _params.put("certificateId", vo.getId());
            List<CertiTransactionInfo> certiTransactionInfoList = certiTransactionInfoService.query(_params);
            vo.setCertiTransactionInfoList(certiTransactionInfoList);
        });


        return certificateInfoVoList;
    }


    /**
     * 根据ID获取数据
     */
    @RequestMapping("/info/{id}")
    @RequiresPermissions("certificate:certificateInfo:info")
    public R info(@PathVariable("id") Long id) {
        CertificateInfoVo certificateInfo = null;
        if (id != null) {
            Map<String, Object> params = new HashMap<>();
            params.put("id", id);
            List<CertificateInfoVo> certificateInfoList = certificateInfoService.selectByJobParams(params);
            if (certificateInfoList != null && certificateInfoList.size() > 0) {
                certificateInfo = certificateInfoList.get(0);
            }
        }

        Map<String, Object> params = new HashMap<>();
        params.put("moduleCode", CommonConstants.ResourceInfo_PIC_Certi);
        params.put("moduleId", id);
        List<ResourceInfo> certiPicList = resourceInfoService.query(params);

        params = new HashMap<>();
        params.put("moduleCode", CommonConstants.ResourceInfo_PIC_Constrat);
        params.put("moduleId", id);
        List<ResourceInfo> constratPicList = resourceInfoService.query(params);

        params = new HashMap<>();
        params.put("moduleCode", CommonConstants.ResourceInfo_PIC_Pay);
        params.put("moduleId", id);
        List<ResourceInfo> payPicList = resourceInfoService.query(params);

        EmployeeInfo employeeInfo = employeeInfoService.findById(certificateInfo.getEmployeeId());
        certificateInfo.setOnCompanyFlag(employeeInfo.getOnCompanyFlag());
        certificateInfo.setCompanyEndTime(employeeInfo.getCompanyEndTime());
        if (certificateInfo.getAffiliationEmployee() != null) {
            employeeInfo = employeeInfoService.findById(certificateInfo.getAffiliationEmployee());
            certificateInfo.setAffiliationEmployeeName(employeeInfo.getName());
        }

        params = new HashMap<>();
        params.put("certificateId", id);
        params.put("type", CommonConstants.CertiTransactionInfoType.BorrowIn);
        long count = certiTransactionInfoService.count(params);
        certificateInfo.setBorrowedInFlag(count > 0);

        certificateInfo.setWarrantAble(1);
        certificateInfo.setWarrantFlag(0);
        certificateInfo.setDetaineeAble(1);
        certificateInfo.setDetaineeFlag(0);
        // 给证书详情添加证书名称
        if (certificateInfo.getStatus().equals(CommonConstants.CertificateStatus.PROJECT_USED)
                || certificateInfo.getStatus().equals(CommonConstants.CertificateStatus.BID_USED)
                || certificateInfo.getStatus().equals(CommonConstants.CertificateStatus.BID_SUCCESS)
                || certificateInfo.getStatus().equals(CommonConstants.CertificateStatus.OTHER_STATUS)) {
            params = new HashMap<>();
            params.put("types", new Integer[]{
                    CommonConstants.CertiTransactionInfoType.Transaction,
                    CommonConstants.CertiTransactionInfoType.Transaction_Return});
            params.put("certificateId", certificateInfo.getId());
            // 押人判断
            CertiTransactionInfoVo certiTransactionInfoVo = new CertiTransactionInfoVo();
            certiTransactionInfoVo.setCertificateId(certificateInfo.getId());
            boolean detaineeFlag = certiTransactionInfoService.detaineeAbleByCertificateVO(certiTransactionInfoVo);
            // 初始化默认值
            certificateInfo.setDetaineeFlag(detaineeFlag ? 1 : 0);
            List<CertiTransactionInfo> transactions = certiTransactionInfoService.query(params);
            if (CollectionUtils.isNotEmpty(transactions)) {
                // 旧数据
                List<CertiTransactionInfo> oldRecord = transactions.stream()
                        .filter(transaction -> transaction.getStatusTag() == null)
                        .collect(Collectors.toList());
                Map<Integer, List<CertiTransactionInfo>> oldRegroup = oldRecord.stream()
                        .collect(Collectors.groupingBy(CertiTransactionInfo::getType));
                int transactionAmount = oldRegroup.get(CommonConstants.CertiTransactionInfoType.Transaction) == null ?
                        0 : oldRegroup.get(CommonConstants.CertiTransactionInfoType.Transaction).size();
                int transactionReturnAmount = oldRegroup.get(CommonConstants.CertiTransactionInfoType.Transaction_Return) == null ?
                        0 : oldRegroup.get(CommonConstants.CertiTransactionInfoType.Transaction_Return).size();
                if (transactionAmount > transactionReturnAmount) {
                    certificateInfo.setProjectNames(transactions.get(0).getProjectName());
                    certificateInfo.setWarrantAble(0);
                    certificateInfo.setWarrantFlag(1);
                    certificateInfo.setDetaineeAble(0);
                    certificateInfo.setDetaineeFlag(1);
                } else {
// 新数据
                    String projectNames = transactions.stream()
                            .filter(transaction -> transaction.getType().equals(CommonConstants.CertiTransactionInfoType.Transaction)
                                    && transaction.getStatusTag().equals(CommonConstants.CertiTransactionStatusTags.IN_PROGRESS.getTag()))
                            .map(CertiTransactionInfo::getProjectName)
                            .collect(Collectors.joining(","));
                    certificateInfo.setProjectNames(projectNames);

                    // 修改押证校验
                    certificateInfo.setWarrantAble(
                            CommonConst.FlagEnum.IS.getId().equals(certificateInfo.getBorrowOutFlag()) ?
                                    CommonConst.FlagEnum.NOT.getId() : CommonConst.FlagEnum.IS.getId());

                    params = new HashMap<>();
                    params.put("employeeId", certificateInfo.getEmployeeId());
                    params.put("borrowOutFlag", CommonConst.FlagEnum.IS.getFlag());
                    List<CertificateInfo> certPoList = certificateInfoService.query(params);
                    // 查询对应的人员的证书是否有借出的，如果有借出的证书，不支持押人
                    certificateInfo.setDetaineeFlag(cn.hutool.core.collection.CollectionUtil.isNotEmpty(certPoList) ?
                            CommonConst.FlagEnum.NOT.getId() : CommonConst.FlagEnum.IS.getId());

                   /* long transactionOutCount = transactions.stream()
                            .filter(transaction -> transaction.getType().equals(CommonConstants.CertiTransactionInfoType.Transaction)
                                    && transaction.getStatusTag().equals(CommonConstants.CertiTransactionStatusTags.IN_PROGRESS.getTag()))
                            .count();
                    long transactionOutWarrantCount = transactions.stream()
                            .filter(transaction -> transaction.getType().equals(CommonConstants.CertiTransactionInfoType.Transaction)
                                    && transaction.getStatusTag().equals(CommonConstants.CertiTransactionStatusTags.IN_PROGRESS.getTag())
                                    && transaction.getWarrantFlag().equals(1))
                            .count();
                    if (transactionOutCount != 0L || transactionOutWarrantCount != 0) {
                        certificateInfo.setWarrantAble(0);
                        certificateInfo.setDetaineeFlag(0);
                        if (transactionOutWarrantCount != 0) {
                            certificateInfo.setWarrantFlag(1);
                        }
                    }*/
                }
            }
        }
        return R.ok().put("certificateInfo", certificateInfo).put("certiPicList", certiPicList).put("constratPicList", constratPicList).put("payPicList", payPicList);
    }

    /**
     * 保存
     */
    @SysLog("新增人员证书")
    @PostMapping("/save")
    @RequiresPermissions("certificate:certificateInfo:save")
    public R save(@RequestBody CertificateInfoVo certificateInfo) {
        boolean existFlag = certificateInfoService.getExistFlag(certificateInfo.getCertificateNo(),
                certificateInfo.getIdCard(), certificateInfo.getProfession());
        if (!existFlag) {
            return R.error("证书已存在");
        }
        certificateInfo.setCreateBy(ShiroUtils.getUserId());
        if (StringUtils.isBlank(certificateInfo.getPIdType())) {
            certificateInfo.setPIdType(certificateInfo.getConfType());
        }
        certificateInfo.setCreateTime(new Date());
        certificateInfoService.save(certificateInfo);
        return R.ok();
    }


    /**
     * 保存
     */
    @SysLog("人员证书批量导入")
    @RequestMapping("/batchImport")
    @RequiresPermissions("certificate:certificateInfo:save")
    public R batchImport(@RequestBody CertificateBo certificateBo) {
        List<R> resultList = new ArrayList<>();
        int countFail = 0;
        if (certificateBo != null && CollectionUtils.isNotEmpty(certificateBo.getCertificateInfos())) {
            List<CertificateInfoVo> certificateInfoVoList = certificateBo.getCertificateInfos();
            certificateInfoVoList.forEach(CertificateFilter::certificateInfoVoTypeId);
            ConfInfo confInfo;
            for (CertificateInfoVo certificateInfoVo : certificateInfoVoList) {
                if (StringUtils.isBlank(certificateInfoVo.getCertificateNo())) {
                    R error = R.error("请填写证书编号");
                    error.put("name", certificateInfoVo.getName());
                    error.put("idCard", certificateInfoVo.getIdCard());
                    error.put("certificateNo", certificateInfoVo.getCertificateNo());
                    resultList.add(error);
                    countFail++;
                    continue;
                }
                if (StringUtils.isBlank(certificateInfoVo.getConfType())) {
                    R error = R.error("证书类型有错误");
                    error.put("name", certificateInfoVo.getName());
                    error.put("idCard", certificateInfoVo.getIdCard());
                    error.put("certificateNo", certificateInfoVo.getCertificateNo());
                    resultList.add(error);
                    countFail++;
                    continue;
                }
                if (!certificateInfoVo.getConfType().startsWith(CommonConstants.CONF_Certificate_Type)) {
                    Map<String, Object> params = new HashMap<>();
                    params.put("name", certificateInfoVo.getConfType());
                    confInfo = confInfoService.findFirst(params);
                    if (confInfo != null) {
                        certificateInfoVo.setPIdType(confInfo.getCode());
                    } else {
                        R error = R.error("证书类型有错误");
                        error.put("name", certificateInfoVo.getName());
                        error.put("idCard", certificateInfoVo.getIdCard());
                        error.put("certificateNo", certificateInfoVo.getCertificateNo());
                        resultList.add(error);
                        countFail++;
                    }
                }
            }
            for (CertificateInfoVo certificateInfo : certificateInfoVoList) {
                Map<String, Object> params = new HashMap<>();
                params.put("certificateNo", certificateInfo.getCertificateNo());
                long count = certificateInfoService.count(params);
                if (count > 0) {
                    R error = R.error("证书已存在");
                    error.put("name", certificateInfo.getName());
                    error.put("idCard", certificateInfo.getIdCard());
                    error.put("certificateNo", certificateInfo.getCertificateNo());
                    resultList.add(error);
                    countFail++;
                    continue;
                }
                if (StringUtils.isBlank(certificateInfo.getPIdType())) {
                    certificateInfo.setPIdType(certificateInfo.getConfType());
                }
                certificateInfo.setKeepDeptId(ShiroUtils.getUserEntity().getDeptId());
                certificateInfo.setCreateBy(ShiroUtils.getUserId());
                certificateInfo.setCreateTime(new Date());
                try {
                    ObjectUtil.trimBean(certificateInfo);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                if (certificateInfo.getValidityEndTime() == null) {
                    certificateInfo.setLongTerm(CommonConstants.CommonStatus.ENABLE);
                }
                if (certificateInfo.getCertificateType() == CommonConstants.CertificateType.SELF) {
                    params = new HashMap<>();
                    params.put("idCard", certificateInfo.getIdCard());
                    List<EmployeeInfo> query = employeeInfoService.query(params);
                    if (CollectionUtils.isEmpty(query)) {
                        R error = R.error("该持证人在当前系统中不存在");
                        error.put("name", certificateInfo.getName());
                        error.put("idCard", certificateInfo.getIdCard());
                        error.put("mobile", certificateInfo.getMobile());
                        resultList.add(error);
                        countFail++;
                        continue;
                    } else {
                        certificateInfo.setEmployeeId(query.get(0).getId());
                        certificateInfoService.save(certificateInfo);
                        continue;
                    }
                }
                if (certificateInfo.getCertificateType() == CommonConstants.CertificateType.PartTime) {
                    if (certificateInfo.getAffiliationEmployeeName() != null) {
                        params = new HashMap<>();
                        params.put("name", certificateInfo.getAffiliationEmployeeName());
                        List<EmployeeInfo> employeeInfos = employeeInfoService.query(params);
                        if (CollectionUtils.isEmpty(employeeInfos)) {
                            R error = R.error("该拿进人在当前系统中不存在");
                            error.put("name", certificateInfo.getAffiliationEmployeeName());
                            resultList.add(error);
                            countFail++;
                            continue;
                        } else {
                            certificateInfo.setAffiliationEmployee(employeeInfos.get(0).getId());
                        }
                    }
                }
                if (certificateInfo.getCertificateType() == CommonConstants.CertificateType.FRIEND) {
                    if (certificateInfo.getFriendshipName() != null && certificateInfo.getFriendshipMobile() != null) {
                        params = new HashMap<>();
                        params.put("name", certificateInfo.getFriendshipName());
                        params.put("mobile", certificateInfo.getFriendshipMobile());
                        List<EmployeeInfo> query = employeeInfoService.query(params);
                        if (CollectionUtils.isNotEmpty(query)) {
                            certificateInfo.setFriendshipEmployeeId(query.get(0).getId());
                        } else {
                            R error = R.error("该非在职员工在当前系统中不存在");
                            error.put("name", certificateInfo.getName());
                            error.put("idCard", certificateInfo.getIdCard());
                            error.put("mobile", certificateInfo.getMobile());
                            resultList.add(error);
                            countFail++;
                            continue;
                        }
                    }
                }
                certificateInfoService.save(certificateInfo);
            }
        }
        R ok = R.ok();
        ok.put("success", certificateBo.getCertificateInfos().size() - countFail)
                .put("fail", countFail)
                .put("data", resultList);
        return ok;
    }


    /**
     * 修改
     */
    @SysLog("更新人员证书")
    @RequestMapping("/update")
    @RequiresPermissions("certificate:certificateInfo:update")
    public R update(@RequestBody CertificateInfoVo certificateInfo) {
        if (StringUtils.isBlank(certificateInfo.getPIdType()) && StringUtils.isBlank(certificateInfo.getConfType())) {
            return R.error("证书类型必须");
        }
        CertificateInfo c = certificateInfoService.findById(certificateInfo.getId());
        // 校验是否有修改权限 修改 20220525 有修改权限，并且是录入人，或者录入人的上级
        UserInfo userInfo = ShiroUtils.getUserEntity();
        if (!userInfo.getId().equals(c.getCreateBy()) && userInfoService.isLowerLevelUserIdOfManagerUser(c.getCreateBy(), userInfo)) {
            throw new RRException(RRExceptionCodeEnum.NO_AUTHORITY);
        }

        if (c != null) {
            if (StringUtils.isBlank(certificateInfo.getPIdType())) {
                certificateInfo.setPIdType(certificateInfo.getConfType());
            }
            // 添加证书编号验证
            if (StrUtil.isNotBlank(certificateInfo.getCertificateNo()) && !c.getCertificateNo().equals(certificateInfo.getCertificateNo())) {
                Map<String, Object> params = new HashMap<>();
                params.put("certificateNo", certificateInfo.getCertificateNo());
                List<CertificateInfo> certificateInfoList = certificateInfoService.query(params);
                if (!certificateInfo.getCertificateNo().startsWith("水木石") && certificateInfoList.size() > 0) {
                    CertificateInfo certificateInfoPo = cn.hutool.core.collection.CollectionUtil.getFirst(certificateInfoList);

                    if (CommonConstants.CertificateStatus.DELETE == certificateInfoPo.getStatus()) {
                        throw new RRException(RRExceptionCodeEnum.CERT_CERTIFICATE_HAS_TO_VOID);
                    }
                    throw new RRException("证书编号重复");
                }
            }
            certificateInfoService.update(certificateInfo);
        }
        return R.ok();
    }

    /**
     * 修改
     */
    @SysLog("修改人员证书")
    @RequestMapping("/updatePro")
    @RequiresPermissions("certificate:certificateInfo:update")
    public R updatePro(@RequestBody CertificateInfoVo certificateInfo) {
        CertificateInfo c = certificateInfoService.findById(certificateInfo.getId());
        if (c != null) {
            certificateInfoService.updatePro(certificateInfo);
        }
        return R.ok();
    }

    /**
     * 人员证书删除
     */
    @SysLog("人员证书删除")
    @DeleteMapping("/{id}")
    @RequiresPermissions("certificate:certificateInfo:delete")
    public RR<Void> delete(@PathVariable Long id) {
        return certificateInfoService.deleteById(id) ? RR.success() : RR.fail();
    }

    /**
     * 删除
     */
    @SysLog("作废人员证书")
    @PostMapping("/delete")
    @RequiresPermissions("certificate:certificateInfo:delete")
    public R delete(Long id, String enableRemark) {
        CertificateInfo c = certificateInfoService.findById(id);
        if (c != null) {
            c.setStatus(CommonConstants.CertificateStatus.DELETE);
            c.setEnableRemark(enableRemark);
            certificateInfoService.update(c);
        } else {
            return R.error("未找到对应证书");
        }
        return R.ok();
    }


    /**
     * 还原
     */
    @SysLog("还原人员证书")
    @RequestMapping("/recover/{id}")
    @RequiresPermissions("certificate:certificateInfo:delete")
    public R recover(@PathVariable("id") Long id) {
        CertificateInfo c;
        c = certificateInfoService.findById(id);
        if (c != null) {
            EmployeeInfo employeeInfo;
            if (c.getCertificateType().equals(CommonConstants.CertificateType.FRIEND)) {
                employeeInfo = employeeInfoService.findById(c.getFriendshipEmployeeId());
            } else {
                employeeInfo = employeeInfoService.findById(c.getEmployeeId());
            }
            boolean existFlag = certificateInfoService.getExistFlagV2(c.getCertificateNo(), employeeInfo.getIdCard(), c.getProfession());
            if (!existFlag) {
                return R.error("已存在相同证书，无法还原");
            }
            c.setStatus(CommonConstants.CertificateStatus.NO_USE);
            certificateInfoService.update(c);
        } else {
            return R.error("请选择还原信息");
        }
        return R.ok();
    }


    /**
     * 上传
     */
    @RequestMapping("/import")
    @RequiresPermissions("certificate:certificateInfo:save")
    public R export(MultipartFile file) {

        if (file.isEmpty()) {
            throw new RRException("上传文件不能为空");
        }

        IdWorker id = new IdWorker(2);
        String fileType = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
        File videoFilePath = new File(othersPath);
        if (!videoFilePath.exists()) {
            videoFilePath.mkdirs();
        }

        if (fileType.toUpperCase().endsWith("XLS") || fileType.toUpperCase().endsWith("XLSX")) {
            String filePath = othersPath + File.separator + id.nextId() + fileType;

            try {
                file.transferTo(new File(filePath));
            } catch (IOException e) {
                e.printStackTrace();
            }

            Map<String, ImportSheetParams> sheetClassParamsMap = new HashMap<>();
            Map<String, Object> excel2Map;
            sheetClassParamsMap.put("Sheet1", new ImportSheetParams("certificateInfoExcel", 1, 1, new String[]{"证书录入类型", "姓名", "身份证号", "手机号码", "证书类型", "证书类别", "专业", "证书是否注册", "证书编号", "非在职员工姓名", "非在职员工关系", "非在职员工联系方式", "发证日期", "证书到期日期", "考试地点", "考试分数", "考试通过猎头", "猎头名称", "猎头费用", "考试日期", "考试总费用", "学历", "费用说明", "备注"}, CertificateInfoExcel.class));
            try {
                excel2Map = ExcelImportUtil.importExcel2Map(new File(filePath), sheetClassParamsMap, null);
            } catch (Exception e) {
                return R.error(e.getMessage());
            }
            List<CertificateInfoExcel> dealInfoExcelList = (List<CertificateInfoExcel>) excel2Map.get("Sheet1");
            if (dealInfoExcelList != null && dealInfoExcelList.size() > 0) {
                if (ObjectUtil.isNotNull(dealInfoExcelList.get(0).getCertificateType())) {
                    if (CommonConstants.CertificateType.PartTime != dealInfoExcelList.get(0).getCertificateType()) {
                        return R.ok().put("dxList", dealInfoExcelList);
                    } else {
                        sheetClassParamsMap.put("Sheet1", new ImportSheetParams("certificateBorrowInExcel", 1, 1, new String[]{"证书录入类型", "姓名", "身份证号", "手机号码", "证书类型", "证书类别", "专业", "证书是否注册", "证书编号", "发证日期", "证书到期日期", "拿进人", "挂靠日期", "结束日期", "人才费用", "挂出接收单位", "挂出接收人", "挂出联系电话", "挂出时间", "挂出结束时间", "挂出收入", "学历", "来源企业名称", "来源联系人", "来源联系方式", "挂靠通过猎头", "猎头名称", "猎头费用", "是否已支付", "是否税后", "支付日期", "费用说明", "备注", "挂出猎头支出", "挂出其他支出", "挂出是否打款", "挂出打款时间", "挂出打款是否税后", "挂出费用说明", "挂出备注"}, CertificateBorrowInExcel.class));
                        try {
                            excel2Map = ExcelImportUtil.importExcel2Map(new File(filePath), sheetClassParamsMap, null);
                        } catch (Exception e) {
                            return R.error(e.getMessage());
                        }
                        List<CertificateBorrowInExcel> dealExcelList = (List<CertificateBorrowInExcel>) excel2Map.get("Sheet1");
                        if (dealExcelList != null && dealExcelList.size() > 0) {
                            return R.ok().put("dxList", dealExcelList);
                        } else {
                            return R.error("表格数据不能为空");
                        }
                    }
                } else {
                    return R.error("文件读取失败，请确定模板正确");
                }

            } else {
                return R.error("表格数据不能为空");
            }
        } else {
            return R.error("请上传 Excel 文件！");
        }
    }


    public Map<String, String> getIdType(String type) {
        Map<String, Object> params = new HashMap<>();
        params.put("type", type);
        params.put("status", CommonConstants.CommonStatus.ENABLE);
        List<ConfInfo> confInfoList = confInfoService.query(params);
        Map<String, String> returnMap = new HashMap<>();
        if (CollectionUtils.isEmpty(confInfoList)) {
            return returnMap;
        }
        confInfoList.forEach((c) -> returnMap.put(c.getName(), c.getName()));

        return returnMap;
    }

    /**
     * 上传
     */
    @RequestMapping("/upload")
    @RequiresPermissions("certificate:certificateInfo:save")
    public R upload(MultipartFile file) {

        if (file.isEmpty()) {
            return R.error("上传文件不能为空");
        }

        IdWorker id = new IdWorker(2);
        String fileName = id.nextId() + "";
        try {
            fileName = FileUtils.transform(file, picPath, fileName, 2, "M", new String[]{FileUtils.JPG, FileUtils.PNG});
        } catch (Exception e) {
            return R.error(e.getMessage());
        }

        return R.ok().put("fileName", fileName).put("remoteUrl", remoteUrl + "pic/");
    }


    /**
     * 批量导出
     */
    @RequestMapping("/export")
    @RequiresPermissions("certificate:certificateInfo:list")
    public void export(HttpServletRequest request, HttpServletResponse response) {

        Map<String, Object> params = new HashMap<>();
        String type = request.getParameter("type");
        String tenantId = request.getParameter("tenantId");
        String status = request.getParameter("status");
        String certificateType = request.getParameter("certificateType");
        String keepDeptId = request.getParameter("keepDeptId");
        String keepUserId = request.getParameter("keepUserId");
        String name = request.getParameter("name");
        String idType = request.getParameter("idType");
        String idCard = request.getParameter("idCard");
        String certificateNo = request.getParameter("certificateNo");
        String mobile = request.getParameter("mobile");
        String profession = request.getParameter("profession");
        String academic = request.getParameter("academic");
        String releaseTime = request.getParameter("releaseTime");
        String validityEndTime = request.getParameter("validityEndTime");
        String validityType = request.getParameter("validityType");
        String months = request.getParameter("months");
        String days = request.getParameter("days");
        String orderBy = request.getParameter("orderBy");
        String inOrder = request.getParameter("inOrder");
        String friendshipEmployeeId = request.getParameter("friendshipEmployeeId");

        if (StringUtils.isNotBlank(certificateType)) {
            params.put("certificateType", Integer.parseInt(certificateType));
        }

        if (StringUtils.isNotBlank(friendshipEmployeeId)) {
            params.put("friendshipEmployeeId", Long.parseLong(friendshipEmployeeId));
        }

        if (StringUtils.isNotBlank(keepDeptId)) {
            params.put("keepDeptId", Long.parseLong(keepDeptId));
        }

        if (StringUtils.isNotBlank(keepUserId)) {
            params.put("keepUserId", Long.parseLong(keepUserId));
        }

        if (StringUtils.isNotBlank(name)) {
            params.put("name", name);
        }

        if (StringUtils.isNotBlank(idType)) {
            params.put("idType", idType);
        }

        if (StringUtils.isNotBlank(idCard)) {
            params.put("idCard", idCard);
        }

        if (StringUtils.isNotBlank(certificateNo)) {
            params.put("certificateNo", certificateNo);
        }

        if (StringUtils.isNotBlank(mobile)) {
            params.put("mobile", mobile);
        }

        if (StringUtils.isNotBlank(profession)) {
            params.put("profession", profession);
        }

        if (StringUtils.isNotBlank(academic)) {
            params.put("academic", academic);
        }

        if (StringUtils.isNotBlank(releaseTime)) {
            params.put("releaseTime", DateUtils.strToDate(releaseTime));
        }

        if (StringUtils.isNotBlank(validityEndTime)) {
            params.put("validityEndTime", DateUtils.strToDate(validityEndTime));
        }

        if (StringUtils.isNotBlank(type)) {
            params.put("confType", type);
        }

        if (StringUtils.isNotBlank(tenantId)) {
            params.put("tenantId", tenantId);
        }

        if (StringUtils.isNotBlank(orderBy)) {
            params.put("orderBy", com.internetCafes.spms.common.utils.StringUtils.HumpToUnderline(orderBy));
        }

        if (StringUtils.isNotBlank(inOrder)) {
            params.put("inOrder", inOrder);
        }

        if (StringUtils.isNotBlank(validityType)) {
            switch (validityType) {
                case "0":
                    params.put("beIvalidity", com.internetCafes.spms.core.utils.DateUtils.getBeAfMonth(Integer.parseInt(months)));
                    break;
                case "-1":
                    params.put("ivalidity", "ivalidity");
                    break;
                case "2":
                    if (StringUtils.isNotBlank(months)) {
                        params.put("warn", com.internetCafes.spms.core.utils.DateUtils.getBeAfMonth(Integer.parseInt(months)));
                    }
                    if (StringUtils.isNotBlank(days)) {
                        params.put("warn", com.internetCafes.spms.core.utils.DateUtils.getBeAfDay(Integer.parseInt(days)));
                    }
                    break;
                default:
                    params.put("validity", com.internetCafes.spms.core.utils.DateUtils.getBeAfMonth(Integer.parseInt(months)));
                    break;
            }
        }

        if (StringUtils.isNotBlank(status)) {
            if ("crash".equals(status)) {
                params.put("status", CommonConstants.CertificateStatus.DELETE);
            } else {
                params.put("status", Integer.parseInt(status));
            }
        } else {
            params.put("statuses", CommonConstants.CertificateStatus.EXCEPT_DELETE);
        }

        List<CertificateInfoVo> certificateInfoVoList = certificateInfoService.selectByJobParams(params);
        try {
            if (certificateInfoVoList != null && certificateInfoVoList.size() > 0) {
                List<Long> idList = certificateInfoVoList.stream().map(CertificateInfoVo::getId).collect(Collectors.toList());
                params = new HashMap<>();
                params.put("idList", idList);
                Map<Long, List<CertificateRenewalInfo>> certiRenewalInfoMap = certificateRenewalInfoService.query(params).stream()
                        .collect(Collectors.groupingBy(CertificateRenewalInfo::getCertificateId));
                Map<Long, List<CertiTransactionInfo>> certiTranscationInfoMap = certiTransactionInfoService.query(params).stream()
                        .collect(Collectors.groupingBy(CertiTransactionInfo::getCertificateId));
                List<CertificateInfoExportExcelPro> orderInfoExcelList = new ArrayList<>();
                CertificateInfoExportExcelPro excel;
                for (CertificateInfoVo certificateInfo : certificateInfoVoList) {
                    excel = new CertificateInfoExportExcelPro();
                    BeanUtils.copyProperties(certificateInfo, excel);
                    List<CertificateRenewalInfo> renewalInfoList = certiRenewalInfoMap.get(certificateInfo.getId());
                    if (CollectionUtils.isNotEmpty(renewalInfoList)) {
                        for (CertificateRenewalInfo c : renewalInfoList) {
                            excel.setCertificateRenewal(StringUtils.isNotBlank(excel.getCertificateRenewal()) ? excel.getCertificateRenewal() + c.toString() : c.toString());
                        }
                    }
                    List<CertiTransactionInfo> transactionInfoList = certiTranscationInfoMap.get(certificateInfo.getId());
                    if (CollectionUtils.isNotEmpty(transactionInfoList)) {
                        for (CertiTransactionInfo c : transactionInfoList) {
                            excel.setCertiTransactions(StringUtils.isNotBlank(excel.getCertiTransactions()) ? excel.getCertiTransactions() + c.toString() : c.toString());
                        }
                    }
                    orderInfoExcelList.add(excel);
                }
                ExcelUtils.exportExcel(orderInfoExcelList, "证书信息统计", "证书信息统计", CertificateInfoExportExcelPro.class, "证书信息统计.xls", response);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @SysLog("智能识别人员证书")
    @PostMapping("/AIRecognize")
    @RequiresPermissions("certificate:certificateInfo:save")
    public R AIRecognize(MultipartFile file) {
        if (file.isEmpty()) {
            return R.error("上传文件不能为空");
        }
        logger.info("fileSize: " + file.getSize());
        if (file.getSize() / 1024 > 1536) {
            return R.error("文件大小不能大于1.5M");
        }
        IdWorker id = new IdWorker(2);
        String fileName = id.nextId() + "";
        if (file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".")).equalsIgnoreCase(FileUtils.PDF)) {
            try {
                fileName = FileUtils.pdfToJpg(file, picPath, fileName);
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {
            try {
                fileName = FileUtils.transform(file, picPath, fileName, 2, "M", new String[]{FileUtils.JPG, FileUtils.PNG});
            } catch (Exception e) {
                return R.error(e.getMessage());
            }
        }

        AIRecognizeInfo aiRecognizeInfo = qrCodeRecognizer.recognize(picPath + File.separator + fileName);
        try {
            if (aiRecognizeInfo == null || ObjectUtil.isAllFieldNull(aiRecognizeInfo)) {
                return R.error("上传图片有误或图片不清晰，请您确认后上传");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        Map<String, Object> params = new HashMap<>();
        params.put("certificateNo", aiRecognizeInfo.getCertificateNo());
        long count = certificateInfoService.count(params);
        if (count > 0) {
            return R.error("您要上传的证书已存在,请重新选择");
        }

        if (aiRecognizeInfo.getProfession() != null && (aiRecognizeInfo.getpIdType() == null || aiRecognizeInfo.getIdType() == null)) {
            String[] idTypeAndPIdType = certificateInfoService.selectIdTypeByProfession(aiRecognizeInfo.getProfession());
            if (idTypeAndPIdType != null) {
                aiRecognizeInfo.setpIdType(idTypeAndPIdType[0]);
                aiRecognizeInfo.setIdType(idTypeAndPIdType[1]);
            }
        }
        params = new HashMap<>();
        params.put("name", aiRecognizeInfo.getName());
        List<EmployeeInfo> query = employeeInfoService.query(params);
        if (CollectionUtils.isNotEmpty(query)) {
            aiRecognizeInfo.setEmployeeId(query.get(0).getId());
        }
        aiRecognizeInfo.setFileName(fileName);
        aiRecognizeInfo.setRemoteUrl(remoteUrl + File.separator + "pic/");
        R r = R.ok();
        r.put("data", aiRecognizeInfo);
        return r;
    }

    @SysLog("批量智能识别人员证书")
    @PostMapping("/batchMatchAIRecognize")
    @RequiresPermissions("certificate:certificateInfo:save")
    public R batchMatchAIRecognize(@RequestParam("files") MultipartFile[] files) {
        int countFail = 0;
        logger.info("开始批量识别匹配:");
        List<R> resultList = new ArrayList<>();
        if (files.length == 0) {
            logger.info("未选择上传文件");
            return R.error("请您选择需要上传的文件");
        }
        IdWorker id = new IdWorker(2);
        for (MultipartFile file : files) {
            String fileName = id.nextId() + "";
            String filePath = "";
            try {
                fileName = FileUtils.transform(file, picPath, fileName, 2, "M", new String[]{FileUtils.JPG, FileUtils.PNG});
                filePath = remoteUrl + File.separator + "pic/" + fileName;
            } catch (Exception e) {
                logger.error(e.getMessage());
                R error = R.error(e.getMessage());
                error.put("fileName", file.getOriginalFilename());
                error.put("filePath", filePath);
                resultList.add(error);
                countFail++;
                continue;
            }
            AIRecognizeInfo aiRecognizeInfo = qrCodeRecognizer.recognize(picPath + File.separator + fileName);
            try {
                if (aiRecognizeInfo == null || ObjectUtil.isAllFieldNull(aiRecognizeInfo)) {
                    logger.info("上传图片有误或不清晰");
                    R error = R.error("上传图片有误或图片不清晰");
                    error.put("fileName", file.getOriginalFilename());
                    error.put("filePath", filePath);
                    resultList.add(error);
                    countFail++;
                    continue;
                }
            } catch (Exception e) {
                logger.info(e.getMessage());
                R error = R.error(e.getMessage());
                error.put("fileName", file.getOriginalFilename());
                error.put("filePath", filePath);
                resultList.add(error);
                countFail++;
                continue;
            }
            Map<String, Object> params = new HashMap<>();
            params.put("certificateNo", aiRecognizeInfo.getCertificateNo());
            long certificateCount = certificateInfoService.count(params);
            if (certificateCount == 0) {
                logger.info("没有查询到对应证书");
                R error = R.error("没有查询到对应证书");
                error.put("fileName", file.getOriginalFilename());
                error.put("filePath", filePath);
                resultList.add(error);
                countFail++;
                continue;
            }
            if (aiRecognizeInfo.getProfession() != null && (aiRecognizeInfo.getpIdType() == null || aiRecognizeInfo.getIdType() == null)) {
                String[] idTypeAndPIdType = certificateInfoService.selectIdTypeByProfession(aiRecognizeInfo.getProfession());
                if (idTypeAndPIdType != null) {
                    aiRecognizeInfo.setpIdType(idTypeAndPIdType[0]);
                    aiRecognizeInfo.setIdType(idTypeAndPIdType[1]);
                }
            }
            params = new HashMap<>();
            params.put("certificateNo", aiRecognizeInfo.getCertificateNo());
            params.put("idCard", aiRecognizeInfo.getIdCard());
            params.put("profession", aiRecognizeInfo.getProfession());
            params.put("idType", aiRecognizeInfo.getIdType());
            params.put("pIdType", aiRecognizeInfo.getpIdType());
            params.put("releaseTime", aiRecognizeInfo.getReleaseTime());
            params.put("validityEndTime", aiRecognizeInfo.getValidityEndTime());
            params.put("fileName", fileName);
            Integer updateNumber = certificateInfoService.updateCertificateIfNull(params);
            if (updateNumber == 0) {
                logger.info("证书信息无需更新");
                R error = R.error("证书信息无需更新");
                error.put("fileName", file.getOriginalFilename());
                error.put("filePath", filePath);
                countFail++;
                resultList.add(error);
            }
        }
        R result = R.ok();
        result.put("data", resultList);
        result.put("success", files.length - countFail);
        result.put("fail", countFail);
        logger.info("总记录条数:" + files.length + "条,成功条数:" + (files.length - countFail) + "条,失败条数:" + countFail + "条");
        return result;
    }

    @PostMapping("/getCertificateWithoutPic/{page}/{limit}/{type}")
    @RequiresPermissions("certificate:certificateInfo:list")
    public R getCertificateWithoutPic(@PathVariable("page") Integer page, @PathVariable("limit") Integer limit, @PathVariable("type") String type) {
        HashMap<String, Object> params = new HashMap<>();
        if ("all".equals(type)) {
            type = null;
        }
        params.put("confType", type);
        params.put("statuses", CommonConstants.CertificateStatus.EXCEPT_DELETE);
        List<CertificateInfoVo> certificateInfoList = certificateInfoMapper.selectByJobParams(params);
        List<CertificateInfoVo> resultList = new ArrayList<>(certificateInfoList);
        List<Long> moduleIds = new ArrayList<>();
        for (CertificateInfoVo certificateInfoVo : certificateInfoList) {
            moduleIds.add(certificateInfoVo.getId());
        }
        params = new HashMap<>();
        params.put("moduleIds", moduleIds);
        params.put("moduleCode", CommonConstants.ResourceInfo_PIC_Certi);
        List<ResourceInfo> resourceInfoList = resourceInfoService.query(params);
        out:
        for (CertificateInfoVo certificateInfoVo : certificateInfoList) {
            for (ResourceInfo resourceInfo : resourceInfoList) {
                if (certificateInfoVo.getId().equals(resourceInfo.getModuleId())) {
                    continue out;
                }
            }
            resultList.remove(certificateInfoVo);
        }
        DataGrid dataGrid;
        if (resultList.size() > page * limit) {
            dataGrid = new DataGrid(resultList.subList(page * limit - limit, page * limit), resultList.size(), limit, page);
        } else {
            dataGrid = new DataGrid(resultList.subList(page * limit - limit, resultList.size()), resultList.size(), limit, page);
        }
        R r = R.ok();
        r.put("data", dataGrid);
        return r;
    }


    @GetMapping("/getCertificateTypeCollect")
    @RequiresPermissions("certificate:certificateInfo:info")
    public R getCertificateTypeCollect() {
        HashMap<String, Object> params = new HashMap<>();
        params.put("statuses", CommonConstants.CertificateStatus.EXCEPT_DELETE);
        params.put("certificateType", CommonConstants.CertificateType.SELF);
        long selfCount = certificateInfoService.countByJobParams(params);
        params.put("certificateType", CommonConstants.CertificateType.PartTime);
        long partTimeCount = certificateInfoService.countByJobParams(params);
        params.put("certificateType", CommonConstants.CertificateType.FRIEND);
        long friendCount = certificateInfoService.countByJobParams(params);
        return R.ok().put("selfCount", selfCount).put("partTimeCount", partTimeCount).put("friendCount", friendCount).put("totalCount", selfCount + partTimeCount + friendCount);
    }

    @GetMapping("/getCertificateStatusCollect")
    @RequiresPermissions("certificate:certificateInfo:info")
    public R getCertificateStatusCollect() {
        HashMap<String, Object> params = new HashMap<>();
        params.put("status", CommonConstants.CertificateStatus.NO_USE);
        long noUseCount = certificateInfoService.countByJobParams(params);
        params.put("status", CommonConstants.CertificateStatus.BORROW_OUT);
        long borrowOutCount = certificateInfoService.countByJobParams(params);
        params.put("status", CommonConstants.CertificateStatus.BID_USED);
        long bidUsedCount = certificateInfoService.countByJobParams(params);
        params.put("status", CommonConstants.CertificateStatus.PROJECT_USED);
        long projectCount = certificateInfoService.countByJobParams(params);
        params.put("status", CommonConstants.CertificateStatus.BID_SUCCESS);
        long bidSuccessCount = certificateInfoService.countByJobParams(params);
        params.put("status", CommonConstants.CertificateStatus.OTHER_STATUS);
        long OtherCount = certificateInfoService.countByJobParams(params);
        params.put("status", CommonConstants.CertificateStatus.BORROW_IN_RETURN);
        long borrowInReturnCount = certificateInfoService.countByJobParams(params);
        params.put("status", CommonConstants.CertificateStatus.BORROW_IN);
        long borrowInCount = certificateInfoService.countByJobParams(params);
        long total = noUseCount + bidSuccessCount + bidUsedCount + borrowOutCount + projectCount + OtherCount + borrowInCount + borrowInReturnCount;
        double noUsePercent = (double) noUseCount / total * 100;
        double borrowOutPercent = (double) borrowOutCount / total * 100;
        double bidUsedPercent = (double) bidUsedCount / total * 100;
        double projectPercent = (double) projectCount / total * 100;
        double bidSuccessPercent = (double) bidSuccessCount / total * 100;
        double otherPercent = (double) OtherCount / total * 100;
        double borrowInReturnPercent = (double) borrowInReturnCount / total * 100;
        double borrowInPercent = (double) borrowInCount / total * 100;
        return R.ok().put("noUseCount", noUseCount)
                .put("noUsePercent", String.format("%.1f", noUsePercent))
                .put("borrowOutCount", borrowOutCount)
                .put("borrowOutPercent", String.format("%.1f", borrowOutPercent))
                .put("bidUsedCount", bidUsedCount)
                .put("bidUsedPercent", String.format("%.1f", bidUsedPercent))
                .put("projectCount", projectCount)
                .put("projectPercent", String.format("%.1f", projectPercent))
                .put("bidSuccessCount", bidSuccessCount)
                .put("bidSuccessPercent", String.format("%.1f", bidSuccessPercent))
                .put("borrowInReturnCount", borrowInReturnCount)
                .put("borrowInReturnPercent", String.format("%.1f", borrowInReturnPercent))
                .put("borrowInCount", borrowInCount)
                .put("borrowInPercent", String.format("%.1f", borrowInPercent))
                .put("otherPercent", String.format("%.1f", otherPercent))
                .put("OtherCount", OtherCount);
    }

    @GetMapping("/getFeeStatistics")
    @RequiresPermissions("certificate:certificateInfo:info")
    public R getFeeStatistics() {
        HashMap<String, Object> params = new HashMap<>();
        params.put("statuses", CommonConstants.CertificateStatus.EXCEPT_DELETE);
        List<CertificateInfo> certificateInfoList = certificateInfoService.query(params);
        List<CertiTransactionInfo> certiTransactionInfoList = certiTransactionInfoService.query(params);
        List<CertificateRenewalInfo> certificateRenewalInfoList = certificateRenewalInfoService.query(params);
        double borrowOutIncome = 0;
        double borrowInPay = 0;
        double selfFee = 0;
        double friendFee = 0;
        double renewalFee = 0;
        if (CollectionUtils.isNotEmpty(certificateInfoList)) {
            for (CertificateInfo certificateInfo : certificateInfoList) {
                if (certificateInfo.getCertificateType() == CommonConstants.CertificateType.SELF) {
                    selfFee += certificateInfo.getExamFee();
                }
                if (certificateInfo.getCertificateType() == CommonConstants.CertificateType.FRIEND) {
                    friendFee += certificateInfo.getExamFee();
                }
            }
        }
        if (CollectionUtils.isNotEmpty(certiTransactionInfoList)) {
            for (CertiTransactionInfo certiTransactionInfo : certiTransactionInfoList) {
                if (certiTransactionInfo.getType().equals(CommonConstants.CertiTransactionInfoType.BorrowIn)) {
                    if (certiTransactionInfo.getAffiliationExpend() != null) {
                        borrowInPay += certiTransactionInfo.getAffiliationExpend();
                    }
                    if (certiTransactionInfo.getAgencyFee() != null) {
                        borrowInPay += certiTransactionInfo.getAgencyFee();
                    }
                }
                if (certiTransactionInfo.getType().equals(CommonConstants.CertiTransactionInfoType.BorrowOut)) {
                    if (certiTransactionInfo.getAffiliationIncome() != null) {
                        borrowOutIncome += certiTransactionInfo.getAffiliationIncome();
                    }
                    if (certiTransactionInfo.getAgencyFee() != null) {
                        borrowOutIncome -= certiTransactionInfo.getAgencyFee();
                    }
                    if (certiTransactionInfo.getOtherFee() != null) {
                        borrowOutIncome -= certiTransactionInfo.getOtherFee();
                    }
                }

            }
        }
        if (CollectionUtils.isNotEmpty(certificateRenewalInfoList)) {
            for (CertificateRenewalInfo certificateRenewalInfo : certificateRenewalInfoList) {
                renewalFee += certificateRenewalInfo.getRenewalFee();
            }
        }
        return R.ok().put("borrowOutIncome", new BigDecimal(borrowOutIncome / 10000).setScale(2, RoundingMode.UP).doubleValue())
                .put("borrowInPay", new BigDecimal(borrowInPay / 10000).setScale(2, RoundingMode.UP).doubleValue())
                .put("selfFee", new BigDecimal(selfFee / 10000).setScale(2, RoundingMode.UP).doubleValue())
                .put("friendFee", new BigDecimal(friendFee / 10000).setScale(2, RoundingMode.UP).doubleValue())
                .put("renewalFee", new BigDecimal(renewalFee / 10000).setScale(2, RoundingMode.UP).doubleValue());

    }

    @GetMapping("/getCertificatePIdTypeCollect")
    @RequiresPermissions("certificate:certificateInfo:info")
    public R getCertificatePIdTypeCollect() {
        HashMap<String, Object> params = new HashMap<>();
        params.put("type", CommonConstants.CONF_Certificate_Type);
        List<ConfInfo> query = confInfoService.query(params);
        params = new HashMap<>();
        List<CertificateInfoVo> certificateInfos;
        params.put("statuses", CommonConstants.CertificateStatus.EXCEPT_DELETE);
        R ok = R.ok();
        for (ConfInfo confInfo : query) {
            params = new HashMap<>();
            params.put("pIdType", confInfo.getCode());
            params.put("statuses", CommonConstants.CertificateStatus.EXCEPT_DELETE);
            long count = certificateInfoService.countByJobParams(params);
            ok.put(confInfo.getCode(), count);
        }
        return ok;
    }

    @GetMapping("/getCertificateIdTypeCollect")
    @RequiresPermissions("certificate:certificateInfo:info")
    public R getCertificateIdTypeCollect(String type) {
        Map<String, Object> params = new HashMap<>();
        params.put("type", type);
        List<ConfInfo> query = confInfoService.query(params);
        params = new HashMap<>();
        params.put("statuses", CommonConstants.CertificateStatus.EXCEPT_DELETE);
        R ok = R.ok();
        Map<String, Object> resultMap;
        List resultList = new ArrayList();
        for (ConfInfo info : query) {
            resultMap = new HashMap<>();
            params.put("pIdType", type);
            params.put("idType", info.getName());
            long count = certificateInfoService.countByJobParams(params);
            resultMap.put("idType", info.getValue());
            resultMap.put("count", count);
            resultList.add(resultMap);
        }
        resultList.sort((Comparator<HashMap>) (o1, o2) -> (int) ((long) (o2.get("count")) - (long) (o1.get("count"))));
        ok.put("data", resultList);
        return ok;
    }

    @GetMapping("/countValidityWarning")
    @RequiresPermissions("certificate:certificateInfo:info")
    public R countValidityWarning() {
        Map<String, Object> params = new HashMap<>();
        params.put("warn", com.internetCafes.spms.core.utils.DateUtils.getBeAfMonth(3));
        params.put("statuses", CommonConstants.CertificateStatus.EXCEPT_DELETE);
        long certiCount = certificateInfoService.countByJobParams(params);
        params.put("warn", com.internetCafes.spms.core.utils.DateUtils.getBeAfDay(15));
        params.put("type", CommonConstants.CertiTransactionInfoType.BorrowIn);
        long borrowInCount = certiTransactionInfoService.countByJobParams(params);
        params.put("warn", com.internetCafes.spms.core.utils.DateUtils.getBeAfDay(15));
        params.put("type", CommonConstants.CertiTransactionInfoType.BorrowOut);
        long borrowOutCount = certiTransactionInfoService.countByJobParams(params);
        params.put("warn", com.internetCafes.spms.core.utils.DateUtils.getBeAfDay(15));
        params.put("type", CommonConstants.CertiTransactionInfoType.Transaction);
        long transactionCount = certiTransactionInfoService.countByJobParams(params);

        return R.ok().put("certificateCount", certiCount)
                .put("borrowInCount", borrowInCount)
                .put("borrowOutCount", borrowOutCount)
                .put("transactionCount", transactionCount);
    }

    @GetMapping("/count")
    @RequiresPermissions("certificate:certificateInfo:info")
    public R count(@RequestParam Map<String, Object> params, HttpServletRequest request) {
        String type = request.getParameter("type");
        String tenantId = request.getParameter("tenantId");
        String status = request.getParameter("status");
        String certificateType = request.getParameter("certificateType");
        String keepDeptId = request.getParameter("keepDeptId");
        String keepUserId = request.getParameter("keepUserId");
        String name = request.getParameter("name");
        String idType = request.getParameter("idType");
        String idCard = request.getParameter("idCard");
        String certificateNo = request.getParameter("certificateNo");
        String mobile = request.getParameter("mobile");
        String profession = request.getParameter("profession");
        String academic = request.getParameter("academic");
        String releaseTime = request.getParameter("releaseTime");
        String validityEndTime = request.getParameter("validityEndTime");
        String validityType = request.getParameter("validityType");
        String months = request.getParameter("months");
        String orderBy = request.getParameter("orderBy");
        String inOrder = request.getParameter("inOrder");

        if (StringUtils.isNotBlank(certificateType)) {
            params.put("certificateType", Integer.parseInt(certificateType));
        }

        if (StringUtils.isNotBlank(keepDeptId)) {
            params.put("keepDeptId", Long.parseLong(keepDeptId));
        }

        if (StringUtils.isNotBlank(keepUserId)) {
            params.put("keepUserId", Long.parseLong(keepUserId));
        }

        if (StringUtils.isNotBlank(name)) {
            params.put("name", name);
        }

        if (StringUtils.isNotBlank(idType)) {
            params.put("idType", idType);
        }

        if (StringUtils.isNotBlank(idCard)) {
            params.put("idCard", idCard);
        }

        if (StringUtils.isNotBlank(certificateNo)) {
            params.put("certificateNo", certificateNo);
        }

        if (StringUtils.isNotBlank(mobile)) {
            params.put("mobile", mobile);
        }

        if (StringUtils.isNotBlank(profession)) {
            params.put("profession", profession);
        }

        if (StringUtils.isNotBlank(academic)) {
            params.put("academic", academic);
        }

        if (StringUtils.isNotBlank(releaseTime)) {
            params.put("releaseTime", DateUtils.strToDate(releaseTime));
        }

        if (StringUtils.isNotBlank(validityEndTime)) {
            params.put("validityEndTime", DateUtils.strToDate(validityEndTime));
        }

        if (StringUtils.isNotBlank(type)) {
            params.put("confType", type);
        }

        if (StringUtils.isNotBlank(tenantId)) {
            params.put("tenantId", tenantId);
        }

        if (StringUtils.isNotBlank(orderBy)) {
            params.put("orderBy", com.internetCafes.spms.common.utils.StringUtils.HumpToUnderline(orderBy));
        }

        if (StringUtils.isNotBlank(inOrder)) {
            params.put("inOrder", inOrder);
        }

        if (StringUtils.isNotBlank(validityType)) {
            if ("0".equals(validityType)) {
                params.put("beIvalidity", com.internetCafes.spms.core.utils.DateUtils.getBeAfMonth(Integer.parseInt(months)));
            } else if ("-1".equals(validityType)) {
                params.put("ivalidity", "ivalidity");
            } else {
                params.put("validity", com.internetCafes.spms.core.utils.DateUtils.getBeAfMonth(Integer.parseInt(months)));
            }
        }

        if (StringUtils.isNotBlank(status)) {
            if ("crash".equals(status)) {
                params.put("status", CommonConstants.CertificateStatus.DELETE);
            } else {
                params.put("status", Integer.parseInt(status));
            }
        } else {
            params.put("statuses", CommonConstants.CertificateStatus.EXCEPT_DELETE);
        }
        long count = certificateInfoService.countByJobParams(params);
        return R.ok().put("count", count);
    }

    @GetMapping("/listBalance")
    @RequiresPermissions("certificate:employeeInfo:list")
    public R listBalance(@RequestParam Map<String, Object> params, HttpServletRequest request) {

        Query query = new Query(params);
        params = new HashMap<>();

        String type = request.getParameter("type");
        String tenantId = request.getParameter("tenantId");
        String certificateType = request.getParameter("certificateType");
        String keepDeptId = request.getParameter("keepDeptId");
        String keepUserId = request.getParameter("keepUserId");
        String name = request.getParameter("name");
        String idType = request.getParameter("idType");
        String idCard = request.getParameter("idCard");
        String certificateNo = request.getParameter("certificateNo");
        String mobile = request.getParameter("mobile");
        String profession = request.getParameter("profession");
        String academic = request.getParameter("academic");
        String releaseTime = request.getParameter("releaseTime");
        String validityEndTime = request.getParameter("validityEndTime");
        String orderBy = request.getParameter("orderBy");
        String inOrder = request.getParameter("inOrder");
        String friendshipEmployeeId = request.getParameter("friendshipEmployeeId");
        String empOrFriId = request.getParameter("empOrFriId");
        if (StringUtils.isNotBlank(certificateType)) {
            params.put("certificateType", Integer.parseInt(certificateType));
        }

        if (StringUtils.isNotBlank(empOrFriId)) {
            params.put("empOrFriId", empOrFriId);
        }

        if (StringUtils.isNotBlank(friendshipEmployeeId)) {
            params.put("friendshipEmployeeId", Long.parseLong(friendshipEmployeeId));
        }

        if (StringUtils.isNotBlank(keepDeptId)) {
            params.put("keepDeptId", Long.parseLong(keepDeptId));
        }

        if (StringUtils.isNotBlank(keepUserId)) {
            params.put("keepUserId", Long.parseLong(keepUserId));
        }

        if (StringUtils.isNotBlank(name)) {
            params.put("name", name);
        }

        if (StringUtils.isNotBlank(idType)) {
            params.put("idType", idType);
        }

        if (StringUtils.isNotBlank(idCard)) {
            params.put("idCard", idCard);
        }

        if (StringUtils.isNotBlank(certificateNo)) {
            params.put("certificateNo", certificateNo);
        }

        if (StringUtils.isNotBlank(mobile)) {
            params.put("mobile", mobile);
        }

        if (StringUtils.isNotBlank(profession)) {
            params.put("profession", profession);
        }

        if (StringUtils.isNotBlank(academic)) {
            params.put("academic", academic);
        }

        if (StringUtils.isNotBlank(releaseTime)) {
            params.put("releaseTime", DateUtils.strToDate(releaseTime));
        }

        if (StringUtils.isNotBlank(validityEndTime)) {
            params.put("validityEndTime", DateUtils.strToDate(validityEndTime));
        }

        if (StringUtils.isNotBlank(type)) {
            params.put("confType", type);
        }

        if (StringUtils.isNotBlank(tenantId)) {
            params.put("tenantId", tenantId);
        }

        if (StringUtils.isNotBlank(orderBy)) {
            if ("name".equals(orderBy) || "profession".equals(orderBy) || "certificateNo".equals(orderBy) || "transactionTime".equals(orderBy) || "transactionEndTime".equals(orderBy)) {
                params.put("orderBy", com.internetCafes.spms.common.utils.StringUtils.HumpToUnderline(orderBy));
            } else {
                params.put("orderBy", orderBy);
            }
        }

        if (StringUtils.isNotBlank(inOrder)) {
            params.put("inOrder", inOrder);
        }

        DataGrid dataGrid = certificateInfoService.selectByBalance(params, query.getPage(), query.getLimit());
        if (StringUtils.isBlank(orderBy)) {
            dataGrid.getList().sort((Comparator<Object>) (o1, o2) -> {
                CertificateInfoVo certificateInfoVo = (CertificateInfoVo) o1;
                CertificateInfoVo certificateInfoVo1 = (CertificateInfoVo) o2;
                return certificateInfoVo.getVacancyRate().compareTo(certificateInfoVo1.getVacancyRate());
            });
        }
        return R.ok().put("page", dataGrid);
    }

    /**
     * 批量导出
     */
    @RequestMapping("/exportBalance")
    @RequiresPermissions("certificate:certificateInfo:list")
    public void exportBalance(HttpServletRequest request, HttpServletResponse response) {

        Map<String, Object> params = new HashMap<>();
        String type = request.getParameter("type");
        String tenantId = request.getParameter("tenantId");
        String certificateType = request.getParameter("certificateType");
        String keepDeptId = request.getParameter("keepDeptId");
        String keepUserId = request.getParameter("keepUserId");
        String name = request.getParameter("name");
        String idType = request.getParameter("idType");
        String idCard = request.getParameter("idCard");
        String certificateNo = request.getParameter("certificateNo");
        String mobile = request.getParameter("mobile");
        String profession = request.getParameter("profession");
        String academic = request.getParameter("academic");
        String releaseTime = request.getParameter("releaseTime");
        String validityEndTime = request.getParameter("validityEndTime");
        String orderBy = request.getParameter("orderBy");
        String inOrder = request.getParameter("inOrder");
        String friendshipEmployeeId = request.getParameter("friendshipEmployeeId");
        String empOrFriId = request.getParameter("empOrFriId");

        if (StringUtils.isNotBlank(empOrFriId)) {
            params.put("empOrFriId", empOrFriId);
        }

        if (StringUtils.isNotBlank(certificateType)) {
            params.put("certificateType", Integer.parseInt(certificateType));
        }

        if (StringUtils.isNotBlank(friendshipEmployeeId)) {
            params.put("friendshipEmployeeId", Long.parseLong(friendshipEmployeeId));
        }

        if (StringUtils.isNotBlank(keepDeptId)) {
            params.put("keepDeptId", Long.parseLong(keepDeptId));
        }

        if (StringUtils.isNotBlank(keepUserId)) {
            params.put("keepUserId", Long.parseLong(keepUserId));
        }

        if (StringUtils.isNotBlank(name)) {
            params.put("name", name);
        }

        if (StringUtils.isNotBlank(idType)) {
            params.put("idType", idType);
        }

        if (StringUtils.isNotBlank(idCard)) {
            params.put("idCard", idCard);
        }

        if (StringUtils.isNotBlank(certificateNo)) {
            params.put("certificateNo", certificateNo);
        }

        if (StringUtils.isNotBlank(mobile)) {
            params.put("mobile", mobile);
        }

        if (StringUtils.isNotBlank(profession)) {
            params.put("profession", profession);
        }

        if (StringUtils.isNotBlank(academic)) {
            params.put("academic", academic);
        }

        if (StringUtils.isNotBlank(releaseTime)) {
            params.put("releaseTime", DateUtils.strToDate(releaseTime));
        }

        if (StringUtils.isNotBlank(validityEndTime)) {
            params.put("validityEndTime", DateUtils.strToDate(validityEndTime));
        }

        if (StringUtils.isNotBlank(type)) {
            params.put("confType", type);
        }

        if (StringUtils.isNotBlank(tenantId)) {
            params.put("tenantId", tenantId);
        }

        if (StringUtils.isNotBlank(orderBy)) {
            params.put("orderBy", com.internetCafes.spms.common.utils.StringUtils.HumpToUnderline(orderBy));
        }

        if (StringUtils.isNotBlank(inOrder)) {
            params.put("inOrder", inOrder);
        }

        List<CertificateInfoVo> certificateInfoVoList = certificateInfoService.selectByBalance(params);
        try {
            if (certificateInfoVoList != null && certificateInfoVoList.size() > 0) {
                List<Long> idList = certificateInfoVoList.stream().map(CertificateInfoVo::getId).collect(Collectors.toList());
                params = new HashMap<>();
                params.put("idList", idList);
                Map<Long, List<CertificateRenewalInfo>> certiRenewalInfoMap = certificateRenewalInfoService.query(params).stream()
                        .collect(Collectors.groupingBy(CertificateRenewalInfo::getCertificateId));
                Map<Long, List<CertiTransactionInfo>> certiTranscationInfoMap = certiTransactionInfoService.query(params).stream()
                        .collect(Collectors.groupingBy(CertiTransactionInfo::getCertificateId));
                List<CertificateBalanceInfoExcel> orderInfoExcelList = new ArrayList<>();
                CertificateBalanceInfoExcel excel;
                for (CertificateInfoVo certificateInfo : certificateInfoVoList) {
                    excel = new CertificateBalanceInfoExcel();
                    BeanUtils.copyProperties(certificateInfo, excel);
                    List<CertificateRenewalInfo> renewalInfoList = certiRenewalInfoMap.get(certificateInfo.getId());
                    if (CollectionUtils.isNotEmpty(renewalInfoList)) {
                        for (CertificateRenewalInfo c : renewalInfoList) {
                            excel.setCertificateRenewal(StringUtils.isNotBlank(excel.getCertificateRenewal()) ? excel.getCertificateRenewal() + c.toString() : c.toString());
                        }
                    }
                    List<CertiTransactionInfo> transactionInfoList = certiTranscationInfoMap.get(certificateInfo.getId());
                    if (CollectionUtils.isNotEmpty(transactionInfoList)) {
                        for (CertiTransactionInfo c : transactionInfoList) {
                            excel.setCertiTransactions(StringUtils.isNotBlank(excel.getCertiTransactions()) ? excel.getCertiTransactions() + c.toString() : c.toString());
                        }
                    }
                    excel.setVacancyRate(certificateInfo.getVacancyRate() * 100 + "%");
                    orderInfoExcelList.add(excel);
                }
                ExcelUtils.exportExcel(orderInfoExcelList, "证书收支信息统计", "证书收支信息统计", CertificateBalanceInfoExcel.class, "证书收支信息统计.xls", response);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @PostMapping("/balanceDetailsList")
    @RequiresPermissions("certificate:employeeInfo:list")
    public R listBalanceDetails(@RequestBody Map<String, Object> params) {
        String orderBy = (String) params.get("orderBy");
        if (StringUtils.isNotEmpty(orderBy)) {
            params.put("orderBy", com.internetCafes.spms.common.utils.StringUtils.HumpToUnderline(orderBy));
        }
        Query query = new Query(params);
        params.put("page", query.getPage() - 1 < 0 ? -1 : (query.getPage() - 1) * query.getLimit());
        params.put("rows", query.getLimit());
        // 查找数据
        UserInfo userInfo = ShiroUtils.getUserEntity();
        if (ObjectUtil.isNotNull(userInfo.getTenantId()) && !userInfo.getTenantId().equals(0)) {
            params.put("tenantId", userInfo.getTenantId());
        }
        List<CertificateInfoVo> balanceDetails = certificateInfoService.getBalanceDetails(params);

        if (CollectionUtil.isEmpty(balanceDetails)) {
            return R.ok().put("data", new DataGrid(balanceDetails, 0, query.getLimit(), query.getPage()));
        }
        Long count = certificateInfoService.countBalanceDetails(params);
        setIncomeOutput(balanceDetails);
        DataGrid dataGrid = new DataGrid(balanceDetails, count, query.getLimit(), query.getPage());
        return R.ok().put("data", dataGrid);
    }


    // 设置证书 支出明细，数据库中并没有存储该字段，计算
    private List<CertificateInfoVo> setIncomeOutput(List<CertificateInfoVo> balanceDetails) {
        for (CertificateInfoVo balanceDetail : balanceDetails) {
            int income;
            int output;
            income = 0;
            output = 0;

            // 获取收支类型  挂出、挂入....
            Integer certificateInfoVoType = balanceDetail.getType();
            switch (certificateInfoVoType) {
                case CommonConstants.CertificateBalanceType.INPUT:
                    if (balanceDetail.getExamFee() != 0) {
                        output += balanceDetail.getExamFee();
                    }
                    if (balanceDetail.getExamAgencyFee() != 0) {
                        output += balanceDetail.getExamAgencyFee();
                    }
                    balanceDetail.setOutput(output);
                    break;
                case CommonConstants.CertificateBalanceType.BORROW_IN:
                    if (balanceDetail.getAffiliationExpend() != 0) {
                        output += balanceDetail.getAffiliationExpend();
                    }
                    if (balanceDetail.getAgencyFee() != 0) {
                        output += balanceDetail.getAgencyFee();
                    }
                    balanceDetail.setOutput(output);
                    break;
                case CommonConstants.CertificateBalanceType.BORROW_OUT:
                    if (balanceDetail.getAffiliationIncome() != 0) {
                        income += balanceDetail.getAffiliationIncome();
                        balanceDetail.setIncome(income);
                    }
                    // 中介
                    if (balanceDetail.getAgencyFee() != 0) {
                        output += balanceDetail.getAgencyFee();
                    }
                    if (balanceDetail.getOtherFee() != 0) {
                        output += balanceDetail.getOtherFee();
                    }
                    balanceDetail.setOutput(output);
                    break;
                case CommonConstants.CertificateBalanceType.RENEWAL:
                    if (balanceDetail.getRenewalPay() != 0) {
                        output += balanceDetail.getRenewalPay();
                    }
                    balanceDetail.setOutput(output);
                    break;
            }
        }
        return balanceDetails;
    }

    @PostMapping("/balanceDetailsExport")
    @RequiresPermissions("certificate:employeeInfo:list")
    public void exportBalanceDetails(@RequestBody Map<String, Object> params, HttpServletResponse response) {
        String orderBy = (String) params.get("orderBy");
        if (StringUtils.isNotEmpty(orderBy)) {
            params.put("orderBy", com.internetCafes.spms.common.utils.StringUtils.HumpToUnderline(orderBy));
        }
        List<CertificateInfoVo> balanceDetails = certificateInfoService.getBalanceDetails(params);
        setIncomeOutput(balanceDetails);
        try {
            if (CollectionUtils.isNotEmpty(balanceDetails)) {
                List<CertiBalanceDetailsExcel> orderInfoExcelList = new ArrayList<>();
                CertiBalanceDetailsExcel o;
                for (CertificateInfoVo vo : balanceDetails) {
                    o = new CertiBalanceDetailsExcel();
                    BeanUtils.copyProperties(vo, o);
                    orderInfoExcelList.add(o);
                }
                ExcelUtils.exportExcel(orderInfoExcelList, "证书收支明细统计", "证书收支明细统计", CertiBalanceDetailsExcel.class, "证书收支明细统计" +
                        ".xls", response);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @GetMapping("/warningCount")
    @RequiresPermissions("certificate:employeeInfo:list")
    public R countWarning() {
        Map<String, Object> params = new HashMap<>();
        params.put("beIvalidity", com.internetCafes.spms.core.utils.DateUtils.getBeAfMonth(3));
        long threeMonth = certificateInfoService.count(params);
        params.put("beIvalidity", com.internetCafes.spms.core.utils.DateUtils.getBeAfMonth(1));
        long oneMonth = certificateInfoService.count(params);
        params = new HashMap<>();
        params.put("ivalidity", -1);
        long count = certificateInfoService.count(params);
        return R.ok().put("threeMonth", threeMonth).put("oneMonth", oneMonth).put("count", count);
    }

}
